--! qt:dataset:src
set hive.fetch.task.conversion=more;

use default;
-- Test sort_array() UDF

DESCRIBE FUNCTION sort_array;
DESCRIBE FUNCTION EXTENDED sort_array;

-- Evaluate function against STRING valued keys
EXPLAIN
SELECT sort_array(array("b", "d", "c", "a")) FROM src tablesample (1 rows);

SELECT sort_array(array("f", "a", "g", "c", "b", "d", "e")) FROM src tablesample (1 rows);
SELECT sort_array(sort_array(array("hadoop distributed file system", "enterprise databases", "hadoop map-reduce"))) FROM src tablesample (1 rows);

-- Evaluate function against INT valued keys
SELECT sort_array(array(2, 9, 7, 3, 5, 4, 1, 6, 8)) FROM src tablesample (1 rows);

-- Evaluate function against FLOAT valued keys
SELECT sort_array(sort_array(array(2.333, 9, 1.325, 2.003, 0.777, -3.445, 1))) FROM src tablesample (1 rows);

-- Evaluate function against LIST valued keys
SELECT sort_array(array(array(2, 9, 7), array(3, 5, 4), array(1, 6, 8))) FROM src tablesample (1 rows);

-- Evaluate function against STRUCT valued keys
SELECT sort_array(array(struct(2, 9, 7), struct(3, 5, 4), struct(1, 6, 8))) FROM src tablesample (1 rows);

-- Evaluate function against MAP valued keys
SELECT sort_array(array(map("b", 2, "a", 9, "c", 7), map("c", 3, "b", 5, "a", 1), map("a", 1, "c", 6, "b", 8))) FROM src tablesample (1 rows);


-- Test it against data in a table.
CREATE TABLE dest1_n130 (
	tinyints ARRAY<TINYINT>,
	smallints ARRAY<SMALLINT>,
	ints ARRAY<INT>,
	bigints ARRAY<BIGINT>,
	booleans ARRAY<BOOLEAN>,
	floats ARRAY<FLOAT>,
	doubles ARRAY<DOUBLE>,
	strings ARRAY<STRING>,
	timestamps ARRAY<TIMESTAMP>
) STORED AS TEXTFILE;

LOAD DATA LOCAL INPATH '../../data/files/primitive_type_arrays.txt' OVERWRITE INTO TABLE dest1_n130;

SELECT	sort_array(tinyints), sort_array(smallints), sort_array(ints),
	sort_array(bigints), sort_array(booleans), sort_array(floats),
	sort_array(doubles), sort_array(strings), sort_array(timestamps)
	FROM dest1_n130;
